#!/bin/bash
hive -e "
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
set hive.exec.orc.compression.strategy=COMPRESSION;
use online_edu_dwd;
insert overwrite table dim_customer
select id,
       customer_relationship_id,
       create_date_time,
       update_date_time,
       deleted,
       name,
       idcard,
       birth_year,
       gender,
       phone,
       wechat,
       qq,
       email,
       if(area is not null,area,"未录入") as area,
       leave_school_date,
       graduation_date,
       bxg_student_id,
       creator,
       origin_type,
       origin_channel,
       tenant,
       md_id
from online_edu_ods.customer
where id is not null;
--员工表(维度表)
insert overwrite table dim_employee
select id,
       email,
       real_name,
       phone,
       department_id,
       department_name,
       remote_login,
       job_number,
       cross_school,
       last_login_date,
       creator,
       create_date_time,
       update_date_time,
       deleted,
       scrm_department_id,
       leave_office,
       leave_office_time,
       reinstated_time,
       superior_leaders_id,
       tdepart_id,
       tenant,
       ems_user_name
from online_edu_ods.employee
where id is not null and scrm_department_id is not null;
--班级信息表
insert overwrite table dim_itcast_clazz
select id,
       create_date_time,
       update_date_time,
       deleted,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       itcast_brand,
       clazz_type_state,
       clazz_type_name,
       teaching_mode,
       start_time,
       end_time,
       comment,
       detail,
       uncertain,
       tenant
from online_edu_ods.itcast_clazz
where itcast_school_id is not null and itcast_subject_id is not null;
--部门表
insert overwrite table dim_scrm_department
select id,
       name,
       parent_id,
       create_date_time,
       update_date_time,
       deleted,
       id_path,
       tdepart_code,
       creator,
       depart_level,
       depart_sign,
       depart_line,
       depart_sort,
       disable_flag,
       tenant
from online_edu_ods.scrm_department
where id is not null and name is not null;
--客户申诉表
insert overwrite table fact_customer_appeal
select id,
       customer_relationship_first_id,
       employee_id,
       employee_name,
       employee_department_id,
       employee_tdepart_id,
       if(appeal_status=1 or appeal_status is null,0,1) as appeal_status,
       audit_id,
       audit_name,
       audit_department_id,
       audit_department_name,
       audit_date_time,
       create_date_time,
       update_date_time,
       deleted,
       tenant
from online_edu_ods.customer_appeal
where customer_relationship_first_id is not null;
--意向表
insert overwrite table fact_customer_relationship
select id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       if(origin_type="NETSERVICE","线上","线下"),
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       year(create_date_time) as `year`,
       substring(create_date_time,1,10) as year_month,
       day(create_date_time) as `day`
from online_edu_ods.customer_relationship
where id is not null;
"